﻿
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sproc_Flow_GetMyFlow_V2]') AND type in (N'P', N'PC'))
    DROP PROCEDURE [dbo].[sproc_Flow_GetMyFlow_V2];
GO
CREATE PROCEDURE [dbo].[sproc_Flow_GetMyFlow_V2]
    @StaffName nvarchar(300)

AS
BEGIN
SET NOCOUNT ON;

DECLARE @staffid int;

--得到用户的ID
SELECT @staffid = staff_id FROM dbo.uds_staff WHERE staff_name = @StaffName;
IF ( @@rowcount <> 1 )
begin
    raiserror(N'The specified staff "%s" cannot be found.', 16, 1, @StaffName);
    return -1;
end;

DECLARE @tmpStep TABLE (
    Flow_ID int not null,
    Step_ID int not null
);

--列出所有流程的第一个环节
INSERT INTO @tmpStep SELECT flow_id,Step_id FROM dbo.UDS_Flow_Step WHERE step_id = 1;

SELECT
        A.Flow_ID,
        A.Flow_Name,
        CONVERT(nvarchar(10), A.Build_Date, 120)  Build_Date,
        A.Builder,
        A.Remark,
        A.Style_ID,
        B.Style_NAME,
        m.Step_id,
        (
            SELECT Step_Name 
            FROM dbo.uds_flow_Step 
            WHERE Flow_id = m.flow_id and step_id = m.step_id
        ) AS StepName,
        CONVERT(
            nvarchar(10),
            (
                SELECT TOP 1 Doc_Added_Date 
                FROM dbo.uds_flow_document k 
                WHERE Doc_Builder_ID = @staffid AND flow_id = a.flow_id 
                ORDER BY Doc_Added_Date DESC
            ),
            120) AS LastUseDate,    --流程最后使用时间
        (
            SELECT COUNT(*) 
            FROM dbo.uds_flow_document k 
            WHERE Doc_Builder_ID = @staffid AND flow_id = a.flow_id
        ) AS UsedTimes --流程使用次数
    FROM 
        dbo.uds_flow a,
        dbo.uds_flow_style b,
        @tmpStep m,
        dbo.v_UDS_Flow_Staff_In_Step n
    WHERE
        a.style_id = b.style_id
        and a.flow_id = m.flow_id
        and m.flow_id = n.flow_id
        and m.step_id = n.step_id
        and n.staff_id = @staffid;

END
GO
